﻿'20130222
'資料庫控制
Public Class ControlDeciceDatabase
    Private database As DatabaseIF
    '專案欄位名
    '  Private ProjectNameColumn As String

    Sub New(ByVal input As DatabaseIF)

        Me.database = input
        ' Me.ProjectNameColumn = "專案編號"

    End Sub
    Function getTable() As System.Data.DataTable
        Return database.readTable
    End Function

    '取得專案欄位值
    Function getProjectText(ByVal ProjectNameColumn As String) As String()
        Dim tableName As String = database.readTable.TableName
        Dim sqlText As String = ""
        sqlText = sqlText + "SELECT First(" + tableName + "." + ProjectNameColumn + ") AS " + ProjectNameColumn + ",  Count(" + tableName + "." + ProjectNameColumn + ") AS 重複筆數 "
        sqlText = sqlText + "FROM  " + tableName + " "
        sqlText = sqlText + "GROUP BY " + tableName + "." + ProjectNameColumn + " "
        '  Dim x() As System.Data.DataRow = Me.DataTable.Select(sqlText)
        Dim ProjectDataTable As System.Data.DataTable = Me.database.useSelect(sqlText)
        Dim ProjectText(ProjectDataTable.Rows.Count - 1) As String
        For index As Integer = 0 To ProjectText.Length - 1
            ProjectText(index) = ProjectDataTable.Rows(index)(ProjectNameColumn).ToString
        Next
        Return ProjectText
    End Function
    '使用sql語法
    Function selectDatabase(ByVal sqlstr As String) As System.Data.DataTable

        Return Me.database.useSelect(sqlstr)

    End Function
    '取得分專案表單
    Function selectProject(ByVal ProjectNameColumn As String, ByVal projectName As String) As System.Data.DataTable
        Dim tableName As String = database.readTable.TableName
        Dim sqlText As String = ""
        sqlText = sqlText + "SELECT *"
        sqlText = sqlText + "FROM " + tableName + " "
        sqlText = sqlText + "WHERE (" + tableName + "."+ProjectNameColumn+")='" + projectName + "' "
        '  Dim x() As System.Data.DataRow = Me.DataTable.Select(sqlText)
        Return Me.database.useSelect(sqlText)
    End Function
    Sub close()
        database.close()
    End Sub
End Class
